from flask import Flask, jsonify
import pymysql
from flask_cors import CORS

app = Flask(__name__)
CORS(app)

# 连接数据库
conn = pymysql.connect(
    host='116.204.102.56',       # 主机名（或IP地址）
    port=3306,                   # 端口号，默认为3306
    user='sx_item1',             # 用户名
    password='3kbrtwMn5a4Hfrkw', # 密码
    charset='utf8mb4',           # 设置字符编码
    database='sx_item1'          # 数据库名
)

@app.route('/get_data', methods=['GET'])
def get_data():
    try:
        cursor = conn.cursor()

        cursor.execute("select pl.ID, pl.poem_name, pl.poem_dynasty, pl.poem_author, "
                       "pt.topic_1, pt.topic_2, pt.topic_3, pt.topic_4, pt.topic_5 "
                       "from poem_list pl left join poem_topic pt "
                       "on pl.ID = pt.poem_id order by rand() limit 10")

        rows = cursor.fetchall()

        data = []
        topics= []
        for row in rows:
            poem_data = {
                'ID': row[0],
                'poem_name': row[1],
                'poem_dynasty': row[2],
                'poem_author': row[3],
                'topics': []
            }
            if row[4]:
                poem_data['topics'].append(row[4])
            if row[5]:
                poem_data['topics'].append(row[5])
            if row[6]:
                poem_data['topics'].append(row[6])
            if row[7]:
                poem_data['topics'].append(row[7])
            if row[8]:
                poem_data['topics'].append(row[8])

            data.append(poem_data)

        cursor.close()
        print(data)
        return jsonify({'data': data})

    except Exception as e:
        print(f"Error fetching data: {e}")
        return jsonify({'error': 'Error fetching data'})

    finally:
        conn.close()

if __name__ == '__main__':
    app.run(debug=True)